package com.glsc.ngateway.common.base.repo.mysql.gateway.platform.system;

import com.glsc.ngateway.common.api.oaflow.dto.SystemFlowStatisticsSearchParamDto;
import com.glsc.ngateway.common.base.domain.mysql.gateway.system.SystemMain;
import com.glsc.ngateway.common.api.platform.dto.system.ParamSystemMainDto;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface SystemMainRepository extends JpaRepository<SystemMain, Integer>, JpaSpecificationExecutor<SystemMain> {

    @Query(value = "select m from SystemMain m where 1=1 " +
            "and (coalesce(:#{#paramSearchDto.opIdAuthList}, null) is null or m.opId in (:#{#paramSearchDto.opIdAuthList})) " +
            "and (:#{#paramSearchDto.opId} is null OR m.opId = :#{#paramSearchDto.opId})  " +
            "and (coalesce(:#{#paramSearchDto.opIdList}, null) is null or m.opId in (:#{#paramSearchDto.opIdList})) " +
            "and (:#{#paramSearchDto.opBuild} is null OR m.opBuild = :#{#paramSearchDto.opBuild})  " +
            "and (:#{#paramSearchDto.opSupport} is null OR m.opSupport = :#{#paramSearchDto.opSupport})  " +
            "and (:#{#paramSearchDto.mainClass} is null OR m.mainClass = :#{#paramSearchDto.mainClass})  " +
            "and (:#{#paramSearchDto.systemClass} is null OR m.systemClass = :#{#paramSearchDto.systemClass})  " +
            "and (:#{#paramSearchDto.systemImportance} is null OR m.systemImportance = :#{#paramSearchDto.systemImportance})  " +
            "and (:#{#paramSearchDto.status} is null OR m.status = :#{#paramSearchDto.status})  " +
            "and (:#{#paramSearchDto.belongDeptId} is null OR m.belongDeptId = :#{#paramSearchDto.belongDeptId})  " +
            "and (:#{#paramSearchDto.opName} is null OR m.opName LIKE CONCAT('%',:#{#paramSearchDto.opName},'%') )  " +
            "and (:#{#paramSearchDto.systemClassNo} is null OR m.systemClassNo LIKE CONCAT('%',:#{#paramSearchDto.systemClassNo},'%') )  " +
//            "and (:#{#paramSearchDto.opSupportName} is null OR m.opSupportName LIKE CONCAT('%',:#{#paramSearchDto.opSupportName},'%') )  " +
            "and (:#{#paramSearchDto.systemSummary} is null OR m.systemSummary LIKE CONCAT('%',:#{#paramSearchDto.systemSummary},'%') )  " +
            "and (:#{#paramSearchDto.memberAccount} is null OR " +
                    "(   " +
                        "m.belongDeptId in(select depid from VEhrAdminDepart where leaderAccount= :#{#paramSearchDto.memberAccount})         " +
                        " or m.opId in (select opId from SystemGroup where groupId " +
                        "                in( select depid from VEhrAdminDepart where leaderAccount= :#{#paramSearchDto.memberAccount} )  )   " +
                        " or m.opId in (select opId from SystemDepartment where departmentId " +
                        "                in( select depid from VEhrAdminDepart where leaderAccount= :#{#paramSearchDto.memberAccount} )  )   " +
                        " or  m.opId in (select opId from SystemMember where memberType ='1' and status ='1' and deleted ='0' and memberAccount " +
                        "               in( select account from VEhrAdminStaff where reportTo = :#{#paramSearchDto.memberAccount}) " +
                    ")   " +
            " ))  " +
//            "and (:#{#paramSearchDto.systemCode} is null OR m.systemCode = :#{#paramSearchDto.systemCode} )  " +
//            "and (:#{#paramSearchDto.beginDateLocalDate} is null OR m.processCreateTime >= :#{#paramSearchDto.beginDateLocalDate} )  " +
//            "and (:#{#paramSearchDto.endDateLocalDate} is null OR m.processCreateTime <= :#{#paramSearchDto.endDateLocalDate} )  " +
//            "and (:#{#paramSearchDto.hasProcessEnd} is null OR m.isClose = :#{#paramSearchDto.hasProcessEnd} )  " +
//            "and (coalesce(:#{#paramSearchDto.adminList}, null) is null or m.systemAdminAccount in (:#{#paramSearchDto.adminList})) " +
            "order by m.systemClassNo asc")
    Page<SystemMain> searchByParam(Pageable pageable, ParamSystemMainDto paramSearchDto);

    SystemMain getByOpId(String opId);

    Integer countByOpName(String opName);

    SystemMain findBySystemClassNo(String systemClassNo);

    SystemMain findByOpName(String opName);

    List<SystemMain> findAllBySystemIdIn(List<String> systemIdList);

    List<SystemMain> findAllBySystemIdNotNull();

    List<SystemMain> findAllByProgramIdNotNull();


    @Query(value = " select * from t_gw_system_main a where 1=1 and " +
            "(   a.belong_dept_id in(select depid from gatewaydb.v_ehr_admin_depart where leader_account= :account )         " +
            " or a.op_id in (select op_id from gatewaydb.t_gw_system_group where group_id " +
            "                in( select depid from gatewaydb.v_ehr_admin_depart where leader_account= :account )  )   " +
            " or a.op_id in (select op_id from gatewaydb.t_gw_system_department where department_id " +
            "                in( select depid from gatewaydb.v_ehr_admin_depart where leader_account= :account )  )   " +
            " or  a.op_id in (select op_id from gatewaydb.t_gw_system_member where member_type ='1' and status ='1' and deleted ='0' and member_account " +
            "               in( select account from v_ehr_admin_staff where reportto = :account) )   " +
            ")" , nativeQuery = true)
    List<SystemMain> queryListByAuth(String account);

    @Query(value = "select m from SystemMain m " +
            "left join SystemGroup g on m.opId = g.opId " +
            "where 1=1 " +
//            "and (:#{#params.opGroupId} is null OR g.groupId = :#{#params.opGroupId}) " +
            "and (coalesce(:#{#params.opGroupIdList}, null) is null or g.groupId in (:#{#params.opGroupIdList})) " +
            "and (:#{#params.mainClass} is null OR m.mainClass = :#{#params.mainClass})  " +
            "and (:#{#params.systemClass} is null OR m.systemClass = :#{#params.systemClass})  " +
//            "and (:#{#params.belongDeptId} is null OR m.belongDeptId = :#{#params.belongDeptId})  " +
            "and (coalesce(:#{#params.opNameList}, null) is null or m.opName in (:#{#params.opNameList})) " +
            "and (coalesce(:#{#params.belongDeptIdList}, null) is null or m.belongDeptId in (:#{#params.belongDeptIdList})) " +
            "and (coalesce(:#{#params.systemClassNoList}, null) is null or m.systemClassNo in (:#{#params.systemClassNoList})) " +
            "")
    List<SystemMain> queryByParam(@Param("params") SystemFlowStatisticsSearchParamDto params);

    SystemMain findByProgramId(String valueOf);
}